Database reference guide

HOME

Conditional Clauses

The IF() statement allows expressions to be defined according to conditions:

IF (<condition>, <true case>, <false case> )

When <condition> is true, the clause takes on the value of the <true case> or else it takes on the value of the <false case>. Consider:

IF( Age >= 65 , “RETIRED” , “NOT RETIRED”)

For each row in the table, this will return either "RETIRED" or "NOT RETIRED" depending on the age of the customer in each record. This is an over simplification, since women usually retire earlier than men, we can deal with this problem as follows:

IF( (Sex=”F”) and (Age >=60 ) , “RETIRED” ,

IF( (Sex=”M”) and ( Age >=65 ) , “RETIRED”,”NOT RETIRED”))

The first IF's condition checks if we have a retired female, if so then we return "RETIRED" else we perform another IF to check for retired males, only if this is false do we then return "NOT RETIRED".

Note:

The parts of the multiple condition (Sex=M) and (Age >= 65) are enclosed in parenthesis, without these the expression's condition would be evaluated as:

Sex = M ANDed with age then checked to be >=65

Which is a completely different result. Always use parenthesis to separate such elements.

IF() statements can also be used to add fields containing numbers together:

IF( [Years at address] >= 3, 5, 0 )

+ IF( Income >= 20000, 5, 0 )

+ IF( HomeOwner, 5,0 )

The value of this expression would be between 0 and 5. This expression could be made more powerful using multiple embedded Ifs, such as:

IF( Income < 5000, 0 ,

IF( Income < 10000, 2 ,

IF( Income < 15000, 3

IF( Income < 20000,4, 5 ))))

This would return the value 0 to 5 depending on the income value for the record.

The IF(..) statement can alternatively be expressed in a potentially more readable format:

IF(<condition>) THEN <value> ELSE <value> ENDIF

So that the previous statement could be written:

IF( Income < 5000 ) THEN 0

ELSE IF( Income < 10000) THEN 2

ELSE IF( Income < 15000) THEN 3

ELSE IF( Income < 20000 THEN 4

ELSE 5

ENDIF

In addition to the IF clause, a multiple simultaneous comparison is possible using the SWITCH clause of the basic form:

SWITCH( <value> )

CASE <value1> : <assigned 1> ,

CASE <value2> : < assigned 2> ,

CASE <value3> : < assigned 3> ,

ELSE : <condition>

SWITCH END

The contents of <value> are compared in turn with each of the values in the CASE clauses. When it finds one that matches, it returns the result assigned to that case. If none match, then it returns the result assigned to the ELSE clause.

Note:

The switch statement can only have a maximum of 100 cases.

For example:

SWITCH (Gender)

CASE “M”: “Male”,

CASE “F”: “Female”,

ELSE: “Unknown”

SWITCH END

Note:

The ELSE clause is optional. If omitted, any records that do not match a case will be assigned a Null value. Conversely, if a record matches more than one case, it will be assigned the value from the first matching case.

SWITCH (Income)

CASE < 5000 : 0,

CASE < 10000 : 2,

CASE < 15000 : 3,

CASE < 20000 : 4,

ELSE : 5

SWITCH END

Note:

This expression will evaluate more quickly because the income field is only accessed once.

It is also possible to perform simultaneous multiple comparisons using the CASE clause which can take two forms. The basic form is:

CASE <expression> WHEN <value1> THEN <assigned1> WHEN <value2> THEN <assigned2> ... ELSE <assigned> END

The contents of <value> is compared in turn with each of the values in the WHEN clauses. When it finds one that matches, it returns the result assigned to that case. If none match, then it returns the result assigned to the optional ELSE clause.

For example:

CASE [demo].[person].[Gender] WHEN "M" THEN "Male" WHEN "F" THEN "Female" ELSE "Unknown" END

The other form of the CASE clause allows for a number of defined conditions to be tested. The syntax for this form is:

CASE WHEN <condition1> THEN <assigned1> WHEN <condition2> THEN <assigned2> ... ELSE <assigned> END

Each <condition> is tested in turn. When a condition is met then the value is returned. If none of the conditions are met then it returns the result assigned to the optional ELSE clause.

Note:

A <value> can itself be an expression.

An example of this form is:

CASE WHEN [demo].[person].[Income] > 100 THEN "High" WHEN [demo].[person].[Income] > 50 THEN "Middle" ELSE "Low" END

The DISTINCT expression can be used to return a distinct number for each distinct value.

For example:

Select expr{distinct(gender)}, gender from person;

Returns:

M 2

F 1

M 2

F 1

M 2

	Online & Instructor-Led Courses \| Training Videos \| Webinar Recordings
	© Alterian. All Rights Reserved. \| Privacy Policy \| Legal Notice